package arquitetura.model;

import to.Locacao;
import to.LocacaoMidia;
import to.Midia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import conexao.Conexao;
import conexao.ConexaoImpl;
import exceptions.ConexaoException;
import exceptions.ModelException;

public class ModelLocacaoMidiaImpl implements ModelLocacaoMidia{
	private Conexao conexao;

	public ModelLocacaoMidiaImpl(){
		conexao = ConexaoImpl.getInstancia();
    }

    public void incluir(LocacaoMidia locacaoMidia) throws ConexaoException, ModelException {
    	Connection conex = conexao.conectar();
        int ultimoCodigo = conexao.ultimoCodigo("LocacaoMidia");
        
        String sql = "INSERT INTO locacaoMidia (codigo, locacao, midia, valor_previsto, valor_pago, valor_multa, valor_desconto) VALUES(?,?,?,?,?,?,?)";
                
        try{
            PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setInt(1, ultimoCodigo + 1);
            pstm.setInt(2, locacaoMidia.getLocacao().getCodigo());
            pstm.setInt(3, locacaoMidia.getMidia().getCodigo());
            pstm.setDouble(4, locacaoMidia.getValorPrevisto());
            pstm.setDouble(5, locacaoMidia.getValorPago());
            pstm.setDouble(6, locacaoMidia.getValorMulta());
            pstm.setDouble(7, locacaoMidia.getValorDesconto());
            pstm.executeUpdate();
            pstm.close();                    
        }catch(SQLException e){
            throw new ModelException();
        }finally{
        	  conexao.desconectar(conex);
        }
    }
    
    public void alterar(LocacaoMidia locacaoMidia) throws ConexaoException, ModelException {
		Connection conex = conexao.conectar();
	        
	    String sql = "UPDATE LocacaoMidia SET locacao=?, midia=?, valor_previsto=?, valor_pago=?, valor_multa=?, valor_desconto=? " +
	    			 "WHERE codigo=?";
	    try {
	        PreparedStatement pstm = conex.prepareStatement(sql);	            
	        pstm.setInt(1, locacaoMidia.getLocacao().getCodigo());
            pstm.setInt(2, locacaoMidia.getMidia().getCodigo());
            pstm.setDouble(3, locacaoMidia.getValorPrevisto());
            pstm.setDouble(4, locacaoMidia.getValorPago());
            pstm.setDouble(5, locacaoMidia.getValorMulta());
            pstm.setDouble(6, locacaoMidia.getValorDesconto());	        
	        pstm.setInt(7, locacaoMidia.getCodigo());
	        pstm.executeUpdate();
	        pstm.close();
	    } catch (SQLException e) {
	        throw new ModelException(e.getMessage());
	    }finally{
	    	conexao.desconectar(conex);
	    }
	}
    
    public void excluir(int codigo) throws ConexaoException, ModelException {
		Connection conex = conexao.conectar();
		
		String sql = "DELETE FROM LocacaoMidia WHERE codigo=?";
		
		try{
			PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setInt(1, codigo);
            pstm.executeUpdate();
            pstm.close();
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);
        }
	}
    
    public LocacaoMidia consultar(int codigo) throws ConexaoException, ModelException {
    	LocacaoMidia locacaoMidia = null;
		Locacao locacao = null;
		Midia midia = null;

		Connection conex = conexao.conectar();		
		String sql = "SELECT * FROM Locacao_Midia WHERE codigo = ?";	

		try{
			PreparedStatement pstm = conex.prepareStatement(sql);
			pstm.setInt(1, codigo);
            ResultSet rs = pstm.executeQuery();
            
            if (rs.next()) {
            	locacaoMidia = new LocacaoMidia();
				locacao = new Locacao();	
				midia = new Midia();

				locacao.setCodigo(rs.getInt("locacao"));
				midia.setCodigo(rs.getInt("midia"));

				locacaoMidia.setCodigo(codigo);
				locacaoMidia.setLocacao(locacao);
				locacaoMidia.setMidia(midia);
				locacaoMidia.setValorPrevisto(rs.getDouble("valor_previsto"));
				locacaoMidia.setValorPago(rs.getDouble("valor_Pago"));
				locacaoMidia.setValorMulta(rs.getDouble("valorMulta"));
				locacaoMidia.setValorDesconto(rs.getDouble("valorDesconto"));
			}
            return locacaoMidia;
	    }catch(SQLException e){
	        throw new ModelException(e.getMessage());
	    }finally{
	    	conexao.desconectar(conex);
	    }
	}
    
    public Collection<String[]> listarDevolucao(int Locacao) throws ConexaoException, ModelException{
        Collection<String[]> lista = new ArrayList<String[]>();
                
        Connection conex = conexao.conectar();
        
        String sql = "SELECT Midia, valor_previsto FROM locacaoMidia WHERE Locacao=?";
        
        try{
            Statement stm = conex.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            
            /*verifica se retornou algum registro e cria os Objetos*/
            while( rs.next() ){
            	
                lista.add(new String[] {rs.getString("codigo"), rs.getString("midia"), rs.getString("valor_previsto")});;
            }
            return lista;
        }catch(SQLException e){
            throw new ModelException(e);
        }finally{
        	conexao.desconectar(conex);
        }
    }
    
    public void devolucao(LocacaoMidia locacaoMidia) throws ConexaoException, ModelException{
    	Connection conex = conexao.conectar();
    	String sql;
              
       sql = "UPDATE LocacaoMidia SET valor_pago=?, valor_multa=? WHERE (Locacao=? AND Midia=?)";
               try {
                    PreparedStatement pstm = conex.prepareStatement(sql);
                    pstm.setDouble(1, locacaoMidia.getValorPago());
                    pstm.setDouble(2, locacaoMidia.getValorMulta());
                    pstm.setInt(3, locacaoMidia.getLocacao().getCodigo());
                    pstm.setInt(4, locacaoMidia.getMidia().getCodigo());
                    pstm.executeUpdate();
                    pstm.close();
                } catch (SQLException ex) {
                    throw new ModelException();
                }finally{
                	conexao.desconectar(conex);
                }
    }   
}